package com.ats.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ats.dao.Userdao_xh;
import com.ats.model.Teacher;
import com.ats.model.User;
import com.ats.util.JDBCUtil;
/**
 * 用户功能的DAO层实现类
 * @author xh
 *
 */
public class UserDaoImpl_xh implements Userdao_xh{


	public User getUserByNameAndPsw(String uname, String upsw) {
		// TODO Auto-generated method stub
		User  User = new User();
		StringBuffer  sql = new StringBuffer();
		sql.append("select * from  ats_user ");
		sql.append(" where  uname =? and upsw =?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1,uname);
			pst.setString(2,upsw);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				User.setUid(rs.getInt("uid"));
				User.setUname(rs.getString("uname"));
				User.setUpsw(rs.getString("upsw"));
				User.setTid(rs.getInt("tid"));
				User.setSid(rs.getInt("sid"));
				User.setIid(rs.getInt("iid"));
				User.setFids(rs.getString("fids"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return User;
	}

	public boolean FindUserByNameByPsw(String uname, String upsw) {
		// TODO Auto-generated method stub
		boolean  flag = false;
		StringBuffer  sql = new StringBuffer();
		sql.append("select * from  ats_user ");
		sql.append(" where  uname =? and upsw =?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1,uname);
			pst.setString(2,upsw);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public int getUstateByUid(int uid) {
		// TODO Auto-generated method stub
		int ustate =0;
		StringBuffer  sql = new StringBuffer();
		sql.append("select ustate from ats_user ");
		sql.append(" where uid = ?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1, uid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				ustate =rs.getInt("ustate");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ustate;
	}

	@Override
	public boolean FindUserByName(String uname) {
		// TODO Auto-generated method stub
		boolean  flag = false;
		StringBuffer  sql = new StringBuffer();
		sql.append("select * from  ats_user ");
		sql.append(" where  uname =? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1,uname);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean FindUserByPsw(String upsw) {
		// TODO Auto-generated method stub
		boolean  flag = false;
		StringBuffer  sql = new StringBuffer();
		sql.append("select * from  ats_user ");
		sql.append(" where  upsw =? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1,upsw);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean updateUpswByTid(String uname, int tid,String upsw) {
		// TODO Auto-generated method stub
		boolean  flag = false;
		StringBuffer  sql = new StringBuffer();
		sql.append("update ats_user set upsw =? ");
		sql.append(" where uname= ? and tid= ? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, upsw);
			pst.setString(2, uname);
			pst.setInt(3, tid);
			int i = pst.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean updateUpswByIid(String uname, int iid,String upsw) {
		// TODO Auto-generated method stub
		boolean  flag = false;
		StringBuffer  sql = new StringBuffer();
		sql.append("update ats_user set upsw =? ");
		sql.append(" where uname= ? and iid= ? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, upsw);
			pst.setString(2, uname);
			pst.setInt(3, iid);
			int i = pst.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean updateUpswBySid(String uname,int sid,String upsw) {
		// TODO Auto-generated method stub
		boolean  flag = false;
		StringBuffer  sql = new StringBuffer();
		sql.append("update ats_user set upsw = ? ");
		sql.append(" where uname= ? and sid= ? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, upsw);
			pst.setString(2, uname);
			pst.setInt(3, sid);
			int i = pst.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}


	@Override
	public int findUstateByUname(String uname) {
		// TODO Auto-generated method stub
		int ustate=0;
		StringBuffer  sql = new StringBuffer();
		sql.append("select ustate from ats_user ");
		sql.append(" where uname = ? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1,uname);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				ustate=rs.getInt("ustate");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ustate;
	}

	@Override
	public int findTidByUname(String name,int age,String address,String number,String uname) {
		// TODO Auto-generated method stub
		int tid=0;
		StringBuffer  sql = new StringBuffer();
		sql.append("select ats_user.tid from ats_user  ");
		sql.append(" inner join ats_teacher");
		sql.append(" on ats_user.tid = ats_teacher.tid ");
		sql.append(" where ats_user.uname = ? ");
		sql.append(" and ats_teacher.`name`=? ");
		sql.append(" and ats_teacher.age=? ");
		sql.append(" and ats_teacher.number=?");
		sql.append(" and ats_teacher.address=? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1,uname);
			pst.setString(2,name);
			pst.setInt(3,age);
			pst.setString(4,number);	
			pst.setString(5,address);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				tid=rs.getInt("tid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tid;
	}

	@Override
	public int findIidByUname(String name,int age,String address,String number,String uname) {
		// TODO Auto-generated method stub
		int iid = 0;
		StringBuffer  sql = new StringBuffer();
		sql.append("select ats_user.iid from ats_user  ");
		sql.append(" inner join ats_instructor");
		sql.append(" on ats_user.iid = ats_instructor.iid ");
		sql.append(" where ats_user.uname = ? ");
		sql.append(" and ats_instructor.`name`=? ");
		sql.append(" and ats_instructor.age=? ");
		sql.append(" and ats_instructor.number=?");
		sql.append(" and ats_instructor.address=? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1,uname);
			pst.setString(2,name);
			pst.setInt(3,age);
			pst.setString(4,number);	
			pst.setString(5,address);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				iid=rs.getInt("iid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return iid;
	}

	@Override
	public int findSidByUname(String name,int age,String address,String number,String uname) {
		// TODO Auto-generated method stub
		int sid=0;
		StringBuffer  sql = new StringBuffer();
		sql.append("select ats_user.sid from ats_user ");
		sql.append(" inner join ats_student ");
		sql.append(" on ats_user.sid = ats_student.sid ");
		sql.append(" where ats_user.uname = ? ");
		sql.append(" and ats_student.`name`=? ");
		sql.append(" and ats_student.age=? ");
		sql.append(" and ats_student.number=?");
		sql.append(" and ats_student.address=? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1,uname);
			pst.setString(2,name);
			pst.setInt(3,age);
			pst.setString(4,number);	
			pst.setString(5,address);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				sid=rs.getInt("sid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sid;
	}

}
